-- @owner: songjing20
-- @date: 2024-7-12
-- @testpoint: sys_connect_by_path中的列含null时，结果正确回显

--step1:建表;expect:成功
drop table if exists t_connectby_0036 cascade;
create table t_connectby_0036 (
id numeric NULL,
pid numeric NULL,
c1 varchar2(10)
);

--step2:插入数据;expect:成功
insert into t_connectby_0036 values (5,4,' ');
insert into t_connectby_0036 values (4,3,'1');
insert into t_connectby_0036 values (4,3,'2');
insert into t_connectby_0036 values (3,2,'3');
insert into t_connectby_0036 values (2,1,'4');
insert into t_connectby_0036 values (1,0,null);

--step3:创建视图，使用sys_connect_by_path查询;expect:成功
create view v_connect_0036 as select * from t_connectby_0036 ;
select sys_connect_by_path(c1 , '_') as pathh
from v_connect_0036 p
connect by prior p.id = pid
start with id = 1 order by 1;

--step4:清理环境;expect:成功
drop table if exists t_connectby_0036 cascade;
